package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.openkoala.security.shiro.CurrentUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.SystemConfigApplication;
import com.dhcc.DRGsHosp.core.domain.SizerCondition;
import com.dhcc.DRGsHosp.infra.util.GlobalData;
import com.dhcc.DRGsHosp.infra.util.StringUtils;
/**
 * @title		:SystemConfigApplicationImpl.java
 * @description	:对drgs.T_CONFIG增删改查ApplicationImpl层
 * @author		:gehui
 * @date		:2016年11月30日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named
@Transactional
public class SystemConfigApplicationImpl implements SystemConfigApplication {
	 @Autowired  
     private JdbcTemplateObj jdbcTemplateObj;
	 
	private EntityRepository getRepository() {
		EntityRepository repository = null;
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
	 /*   定义DRGs数据源*/
		private QueryChannelService queryChannel_DRGs;

	    private QueryChannelService getQueryChannelDRGsService(){
	       if(queryChannel_DRGs==null){
	    	   queryChannel_DRGs = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel_DRGs");
	       }
	     return queryChannel_DRGs;
	    }
	    
	//全表查询配置表T_CONFIG
	@Override
	public List<Map<String,Object>> systemConfig(){
		String sql="select cfg_code,cfg_desc,(case cfg_isdic when '0' then cfg_value else dic_desc end) cfg_value,cfg_valid,cfg_isdic,cfg_resume from drgs.T_CONFIG t  left join drgs.MR_DICTIONARYDATA m on t.cfg_code=m.dic_code and t.cfg_value=m.dic_value";
		List<Map<String,Object>> mapList =  jdbcTemplateObj.queryForList(sql) ;
		return mapList;
	}
	
	
	//按字典项代码查询字典表MR_DICTIONARYDATA
	@Override
	public List<Object[]> dicSearch(String param){
		List<Object[]> list =new LinkedList<>();
		String sql="select dic_code,dic_value,dic_desc from drgs.MR_DICTIONARYDATA t where dic_code='"+param+"' order by dic_value";
		list=getRepository().createSqlQuery(sql).list();
		return list;
	}
	
	//添加/更新配置项
	@Override
	public void saveCfg(Map<String, Object> json) {
		String cfg_code = json.get("cfg_code")!=null?json.get("cfg_code").toString():"";
		String cfg_desc = json.get("cfg_desc")!=null?json.get("cfg_desc").toString():"";
		String cfg_value = json.get("cfg_value")!=null?json.get("cfg_value").toString():"";
		String cfg_valid = json.get("cfg_valid")!=null?json.get("cfg_valid").toString():"";
		String cfg_isdic = json.get("cfg_isdic")!=null?json.get("cfg_isdic").toString():"";
		String cfg_resume = json.get("cfg_resume")!=null?json.get("cfg_resume").toString():"";
		String sqlStr = "select 1 from drgs.t_config t where t.cfg_code='"+cfg_code+"' ";
		List<Object[]> list = getRepository().createSqlQuery(sqlStr).list();
		if(list.size()>0){
			Map<String, Object> param = new HashMap<>();
			param.put("cfg_code", cfg_code);
			param.put("cfg_desc", cfg_desc);
			param.put("cfg_value", cfg_value);
			param.put("cfg_valid", cfg_valid);
			param.put("cfg_isdic", cfg_isdic);
			param.put("cfg_resume", cfg_resume);
			sqlStr = "update drgs.t_config set cfg_desc=:cfg_desc, cfg_value=:cfg_value, cfg_valid=:cfg_valid, cfg_isdic=:cfg_isdic, cfg_resume=:cfg_resume where cfg_code=:cfg_code";
			getRepository().createSqlQuery(sqlStr).setParameters(param).executeUpdate();
		}else{
			Map<String, Object> param = new HashMap<>();
			param.put("cfg_code", cfg_code);
			param.put("cfg_desc", cfg_desc);
			param.put("cfg_value", cfg_value);
			param.put("cfg_valid", cfg_valid);
			param.put("cfg_isdic", cfg_isdic);
			param.put("cfg_resume", cfg_resume);
			sqlStr = "insert into drgs.t_config (cfg_code, cfg_desc, cfg_value, cfg_valid, cfg_isdic, cfg_resume) values (:cfg_code, :cfg_desc, :cfg_value, :cfg_valid, :cfg_isdic, :cfg_resume)";
			getRepository().createSqlQuery(sqlStr).setParameters(param).executeUpdate();
		}
	}
	
	//删除配置项
	@Override
	public void delCfg(String param){
		String sql = "select 1 from drgs.t_config t where cfg_code='"+param+"'";
		List<Object[]> list = getRepository().createSqlQuery(sql).list();
		sql = " begin \n ";
		if(list.size()>0){
			sql += "delete from drgs.t_config t where t.cfg_code='"+param+"'; \n ";
		}
		sql += "end; \n ";
		getRepository().createSqlQuery(sql).executeUpdate();
	}

	@Override
	public List<Object[]> getConfigValue() {
		List<Object[]> list =new ArrayList<Object[]>();
		String sql="select cfg_code,cfg_value from drgs.T_CONFIG";
		list = getRepository().createSqlQuery(sql).list();
		return list;
	}
	
/**
 *  @Description：获取表结构的属性名和注释名称
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#findTableFiles(java.lang.String)
 */
	@Override
	public List<Map<String,Object>> findTableFiles(String tableName) {
		
		String sql="select tc.COLUMN_NAME,cc.COMMENTS,tc.DATA_TYPE,tc.TABLE_NAME from sys.user_tab_columns tc left join sys.all_col_comments cc on tc.TABLE_NAME = cc.TABLE_NAME and tc.COLUMN_NAME = cc.COLUMN_NAME ";
		if(!tableName.equals("all")){
			sql+="where tc.TABLE_NAME ='"+tableName+"' \n ";
		}else{
			sql+="where tc.TABLE_NAME in('MR_APPENDPAGE','MR_BASEINFO','MR_COST','MR_DIAGNOSE','MR_OPERATION','MR_IPINFO') \n ";
		}
		  sql+= "\n order by tc.COLUMN_ID";
		return jdbcTemplateObj.queryForList(sql);
	}
	
	/**
	 * 
	 *  @Description：获取最大分组时间
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.DrgsIndexCalApplication#getMaxGroupYear()
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getMaxGroupYear'")
	public String getMaxGroupYear() {
		String roleName = CurrentUser.getRoleName();
		String hospName = GlobalData.hospName;
		StringBuilder sql = new StringBuilder();
		sql.append(" select max(P00290000) from drgs.MR_IPINFO a \n ");
		sql.append(" left join drgs.t_patientdrg b \n ");
		sql.append(" on a.PMAININDEX=b.parid \n ");
		sql.append(" where b.parid is not null \n ");
		if(StringUtils.hasLength(hospName)&&"清华长庚".equals(hospName)&&StringUtils.hasLength(roleName)&&"科室".equals(roleName)){
			sql.append(" and a.confirmflag=1 \n ");
		}
		List<Object> resultList = getRepository().createSqlQuery(sql.toString()).list();
		if(resultList.size()>0){
			return resultList.get(0)!=null?resultList.get(0).toString().substring(0, 10):null;
		}
		return null;
	}
	
	/**
	 * 
	 * TODO 获取可配置的医院指标
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getHospQuota()
	 */
	@Override
	public List<Map<String, Object>> getHospQuota() {
		String  sql="SELECT T.MS_CODE MSCODE,M.DIC_DESC MSDESC,t.ms_flag MSFLAG FROM DRGS.T_MANAGESUMMARY T \r\n" + 
				"left join drgs.MR_DICTIONARYDATA m on T.MS_CODE=m.dic_value\r\n" + 
				"WHERE T.MS_CODE  <> 'manager_inner' AND T.MS_CODE NOT LIKE 'std_%'  and T.MS_CODE  is not null and "
				+ "M.DIC_DESC  is not null";
		return jdbcTemplateObj.queryForList(sql);
	}
	/**
	 * Description ： 更新可配置的医院指标
	 * date: 2017年7月17日 下午3:54:53  
	 * @author ShaozhiYuan
	 * @version V1.0 DHC
	 */
	@Override
	public void updateHospQuota(String dataHospQuote,String flag) {
  		String sql="update drgs.t_managesummary set ms_flag='"+flag+"' where   ms_code='"+dataHospQuote+"' \n";
  	 getRepository().createSqlQuery(sql).executeUpdate();
	}
/**
 * 
 *  @Description：获取年度标杆
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getStdYear()
 */
	@Override
	public Object getStdYear() {
		String sql="SELECT distinct a.as_year FROM drgs.t_annualstd a order by a.as_year desc";
		return jdbcTemplateObj.queryForList(sql);
	}
	
/**
 *  getStdInfo.  
 *  @Description：获取标杆信息
 *  @Author fulianxia
 *  @return 
 *  @Date:2018年1月17日
 */
public Map<String,Object> getStdInfo(){
	Map<String, Object> stdInfo = new HashMap<String,Object>();
	String sql = "SELECT distinct a.as_year FROM drgs.t_annualstd a order by a.as_year desc";
	List<Map<String,Object>> stdYear = jdbcTemplateObj.queryForList(sql);
	stdInfo.put("stdYear", stdYear);	
	sql = "select distinct std.ver_code,ver_desc,id from DRGs.t_annualstd std left join DRGs.t_Drgversion d on std.ver_code=d.ver_code order by d.ver_code desc ";
	List<Map<String, Object>> groupVersion = jdbcTemplateObj.queryForList(sql);
	stdInfo.put("groupVersion", groupVersion);	
	return stdInfo;
}
	
/**
 * 
 *  @Description：获取年度标杆
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getStdAvgFee(java.lang.String)
 */
@Override
public Object getStdAvgFee( String stdId,String adjust) {
	String sql="SELECT a.as_drg_code,a.primeval_avg_fee,a.primeval_avg_fee * "+adjust+" as_avg_fee FROM drgs.t_annualstd a where a.as_id='"+stdId+"' ";
	return jdbcTemplateObj.queryForList(sql);
}
/**
 * 
 *  @Description：：获取次均费用调整参数
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getAvgFeeConfig()
 */
@Override
public Object getAvgFeeConfig() {
	String sql="select t.cfg_value from drgs.T_CONFIG t where t.cfg_code='AVGFEE_ADJUST'";
	return jdbcTemplateObj.queryForList(sql);
}
/**
 * 
 *  @Description：更新调整后的次均费用
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#saveAvgFeeAdjust(java.lang.String, java.lang.String)
 */
@Override
public Object saveAvgFeeAdjust(String stdId, String adjust) {
	String sql="update drgs.t_annualstd set as_avg_fee =primeval_avg_fee * "+adjust+"  where as_id='"+stdId+"'";
	return jdbcTemplateObj.update(sql);
}

/**
 * 
 *  @Description：获取医嘱开单科室
 *  @Author WENYI  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getOrderDep()
 */
@Override
public Object getOrderDep() {
	String sql="select order_doc_dep_code depCode,order_doc_dep depDesc,is_special_dep isSpecial from DRGS.t_order_dep";
	return jdbcTemplateObj.queryForList(sql);
}
/**
 * 
 *  @Description：保存特殊部门配置
 *  @Author WENYI  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#saveSpecialDep(java.lang.String)
 */
@Override
public int saveSpecialDep(String deps) {
	String depArray[]=deps.split(",");
	String sql = "";
	sql += " BEGIN \n";
	sql +=      "UPDATE DRGS.T_ORDER_DEP SET IS_SPECIAL_DEP=0 ;\n";
	for(int i=0;i<depArray.length;i++){
		if(StringUtils.hasLength(depArray[i])){
			sql +=	"UPDATE DRGS.T_ORDER_DEP SET IS_SPECIAL_DEP=1 WHERE ORDER_DOC_DEP_CODE='"+depArray[i]+"' ;\n";
		}
	}
	sql += " 	COMMIT;\n";
	sql += " END;";
	int i=getRepository().createSqlQuery(sql).executeUpdate();
	return i;
}
/**
 * 
 *  @Description：获取医师配置
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getDoctorConfig()
 */
@Override
public Map<String, Object> getDoctorConfig() {
	String sql="select t.cfg_value doctor from drgs.T_CONFIG t WHERE t.cfg_code='DOCTOR_SET'";
	List<Map<String,Object>> list=jdbcTemplateObj.queryForList(sql);
	if(!list.isEmpty()) {
		return list.get(0);
	}
	return null;
}
/**
 * 
 *  @Description：获取医保类型有哪些
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getMedisType()
 */
@Override
public List<String> getMedisType() {	
	return jdbcTemplateObj.queryForList("SELECT Distinct t.hi_type FROM drgs.t_medins t",String.class);
}
/**
 *  @Description：初始化标杆管理表
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#initializeManageSummary()
 */
@Override
public void initializeManageSummary() {	
    String sql="merge into drgs.T_MANAGESUMMARY a\r\n" + 
		"USING (\r\n" + 
		"select 'std_computer' AS ms_code,'0' AS ms_flag from dual\r\n" + 
		") b\r\n" + 
		"on (a.ms_code = b.ms_code)\r\n" + 
		"when matched then\r\n" + 
		"update set a.ms_flag = b.ms_flag \r\n" + 
		"when not matched then\r\n" + 
		"insert values(b.ms_code,b.ms_flag)";
    jdbcTemplateObj.execute(sql);
     sql="merge into drgs.T_MANAGESUMMARY a\r\n" + 
	 		"USING (\r\n" + 
	 		"select 'manager_inner'AS ms_code,'0' AS ms_flag from dual\r\n" + 
	 		") b\r\n" + 
	 		"on (a.ms_code = b.ms_code)\r\n" + 
	 		"when matched then\r\n" + 
	 		"update set a.ms_flag = b.ms_flag \r\n" + 
	 		"when not matched then\r\n" + 
	 		"insert (a.ms_code,a.ms_flag) values(b.ms_code,b.ms_flag)";
     jdbcTemplateObj.execute(sql);
     sql="merge into drgs.T_MANAGESUMMARY a\r\n" + 
	 		"USING (\r\n" + 
	 		"select 'std_paygroup'AS ms_code,'0' AS ms_flag  from dual\r\n" + 
	 		") b\r\n" + 
	 		"on (a.ms_code = b.ms_code)\r\n" + 
	 		"when matched then\r\n" + 
	 		"update set a.ms_flag = b.ms_flag \r\n" + 
	 		"when not matched then\r\n" + 
	 		"insert (a.ms_code,a.ms_flag) values(b.ms_code,b.ms_flag )";
     jdbcTemplateObj.execute(sql);
	}
/**
 *  @Description：：保存用户分组方案
 *  @Author lhj 
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#setSystemGroupScheme(java.lang.String, java.lang.String, java.lang.String)
 */
@Override
public Object setSystemGroupScheme(String userId, String roleId, String groupScheme) {
	SizerCondition sie=new SizerCondition();
	String mouldeId="groupScheme_"+userId+"_"+roleId;
	sie.setMoudkeCode(mouldeId);
	sie.setUserCode(userId);
	sie.setRoleCode(roleId);
	sie.setConditionValue(groupScheme);
	sie.save();
	return true;
}
/**
 *  @Description：：获取用户配置好的分组方案
 *  @Author lhj 
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getSystemGroupScheme(java.lang.String, java.lang.String)
 */
@Override
public String getSystemGroupScheme(String mouldeId,String userId, String roleId ) {
	return jdbcTemplateObj.queryForObject("select a.codition_value from drgs.mr_sizer_condition a where a.moudle_code= '"+mouldeId+"' and a.user_code='"+userId+"' and a.role_code='"+roleId+"' ",String.class);
}
/**
 * 
 *  @Description：获取当前数据含有的分组方案
 *  @Author liuhongjun 
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getMrGroupScheme()
 */
@Override
public List<Map<String, Object>> getMrGroupScheme() {
	return jdbcTemplateObj.queryForList("select distinct d.ver_region from drgs.t_patientdrg d where d.ver_region is not null");

 }
/**
 *  @Description：获取标杆表中现有的分组器
 *  @Author：liuhongjun 
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getGroupVersion()
 */
@Override
public List<Map<String, Object>> getGroupStdType() {
	return jdbcTemplateObj.queryForList("select std.ver_code VERCODE,wm_concat(std.as_std_type) STDTYPE from(\r\n" + 
			"SELECT distinct n.ver_code,n.as_std_type  FROM DRGS.t_Annualstd n order by n.ver_code \r\n" + 
			")std group by std.ver_code");
  }

@Override
public String getGroupByScheme(String groupScheme) {
	return jdbcTemplateObj.queryForObject("SELECT  a.ver_code FROM DRGS.t_Patientdrg a where a.ver_region='"+groupScheme+"' and rownum=1", String.class);
}
/**
 *  @Description：在线分组获取版本
 *  @Author：liuhongjun 
 *  @see com.dhcc.DRGsHosp.application.SystemConfigApplication#getOnlineGroup()
 */
@Override
public Object getOnlineGroup() {
	Map<String, Object> stdInfo = new HashMap<>();
	String  sql = "select d.ver_code,d.ver_desc from drgs.t_drgversion  d order by d.ver_code desc ";
	List<Map<String, Object>> groupVersion = jdbcTemplateObj.queryForList(sql);
	stdInfo.put("groupVersion", groupVersion);	
	return stdInfo;
}
}
